using System;
using System.Collections.Generic;
using System.Text;
using FinanceStats.Entities;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using FinanceStats.Common;
using System.Data.Common;

namespace FinanceStats.DAL
{
    public static class EuroDAO
    {
        private static DbCommand updateCommand = null;

        private static DbCommand GetUpdateCommand(SqlDatabase db)
        {
            if (updateCommand == null)
            {
                updateCommand = db.GetStoredProcCommand("[Acs].[dbo].[UpdateEuro]");
                db.AddInParameter(updateCommand, "@Date", DbType.DateTime);
                db.AddInParameter(updateCommand, "@Amount", DbType.Decimal);
            }
            return updateCommand;
        }

        public static bool SaveEuro(Euro euro)
        {
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetUpdateCommand(db);
            db.SetParameterValue(cmd, "@Date", euro.Date);
            db.SetParameterValue(cmd, "@Amount", euro.Amount);
            return Convert.ToBoolean(db.ExecuteNonQuery(cmd));
        }
        
        private static string selectSQL = "SELECT DATE, AMOUNT FROM ACS.DBO.EUROCOURSE {0} ORDER BY DATE";

        public static Dictionary<DateTime, Decimal?> LoadEuroCourse(string where)
        {
            Dictionary<DateTime, Decimal?> res = new Dictionary<DateTime, decimal?>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
                string select = String.Format(selectSQL, where);
                reader = db.ExecuteReader(CommandType.Text, select);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        DateTime date = reader.GetDateTime(0);
                        decimal? amount = reader.IsDBNull(1) ? null : (Decimal?)reader.GetDecimal(1);
                        res.Add(date, amount);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                throw ex;
            }
            return res;
        }

        public static Decimal GetEuroCourse(DateTime date)
        {
            string sql = String.Format("SELECT AMOUNT FROM ACS.DBO.EUROCOURSE WHERE DATE = '{0}'", date);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            object val = db.ExecuteScalar(CommandType.Text, sql);
            decimal res = val == null ? 0 : (decimal)val;
            return res;
        }
     }
}
